package com.kaifamiao.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class JdbcUtil {

    // 定义常量
    private static final String PREFIX = "jdbc.connection.";
    private static final String SUFFIX_DRIVER = ".driver";
    private static final String SUFFIX_URL = ".url";
    private static final String SUFFIX_USER = ".user";
    private static final String SUFFIX_PASSWORD = ".password";
    private static final String CONNECT = "connect";
    private static final String DEFAULT_CONNECT = "mysql" ;

    /* --- 定义实例变量 --- */
    private final Properties props = new Properties();
    // 数据库连接信息
    private String driverClassName;
    private String url;
    private String user;
    private String password;
    // 用来缓存数据库连接的实例变量
    private Connection conn;

    // 将构造私有
    private JdbcUtil(){
        super();
    }

    // 提供静态工厂方法
    public static JdbcUtil getInstance() {
        return getInstance( "jdbc.properties");
    }

    public static JdbcUtil getInstance( String config ) {
        JdbcUtil ju = new JdbcUtil();
        ju.read( config ); // 读取配置文件
        ju.init(); // 初始化数据库连接信息
        ju.load(); // 加载并注册数据库驱动
        ju.connect(); // 建立数据库连接
        return ju;
    }

    // 读取配置文件
    private void read( String filename ) {
        // 获得当前实例(this)对应的类的 Class实例
        Class<?> c = this.getClass();
        // 获得用于读取 类路径根目录("/") 下 指定文件(filename) 的字节输入流
        InputStream in = c.getResourceAsStream( "/" + filename );
        try {
            // 从字节输入流中加载 "属性名-属性值" 对 到 Properties集合中
            props.load(in);
            // 关闭字节输入流
            in.close();
        } catch ( IOException e ) {
            System.out.println( "读取资源文件时发生错误: " + e.getMessage() );
        }
    }

    // 初始化数据库连接信息
    private void init(){
        String connect = props.getProperty( CONNECT , DEFAULT_CONNECT);
        connect = connect.isBlank() ? DEFAULT_CONNECT : connect;
        driverClassName = props.getProperty(  PREFIX+ connect + SUFFIX_DRIVER );
        url = props.getProperty( PREFIX + connect + SUFFIX_URL );
        user = props.getProperty( PREFIX + connect + SUFFIX_USER );
        password = props.getProperty( PREFIX + connect + SUFFIX_PASSWORD );
    }

    // 加载并注册驱动
    private void load(){
        try {
            Class.forName( driverClassName );
        } catch (ClassNotFoundException cause) {
            String message = String.format( "驱动类%s不存在", driverClassName );
            throw new RuntimeException( message , cause );
        }
    }

    // 建立连接
    private void connect(){
        try {
            conn = DriverManager.getConnection( url, user, password );
        } catch (SQLException cause) {
            String message = String.format( "数据库连接失败" );
            throw new RuntimeException( message , cause );
        }
    }

    /**
     * 根据给定的SQL语句创建 PreparedStatement 对象，并指示是否获取由数据库生成的键
     * @param sql 可能带有参数占位符的SQL语句
     * @param generate 若需要获得由数据库生成的键则传入 true ，否则传入 false 。
     * @return
     */
    private PreparedStatement prepare( String sql , boolean generate){
        try {
            return conn.prepareStatement( sql , generate ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
        } catch (SQLException cause) {
            throw new RuntimeException( "创建Statement失败" , cause );
        }
    }

    // 为PreparedStatement设置参数占位符的值
    private void setParameters( PreparedStatement ps , Object... params ) {
        if( params != null && params.length > 0 ) {
            try {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            } catch ( SQLException cause ) {
                throw new RuntimeException( "SQL占位符参数设置错误", cause );
            }
        }
    }

    /**
     * "执行"DML语句并返回受语句影响的记录数目
     * @param sql 可能带有`SQL参数占位符`的DML语句
     * @param params 若第一个参数中包含`SQL参数占位符`，则需要通过变长参数依次传入各个占位符的值
     * @return
     */
    public int execute( String sql, Object... params ){
        PreparedStatement ps = this.prepare(sql, false);
        setParameters( ps , params );
        try {
            return ps.executeUpdate();
        } catch (SQLException cause) {
            throw new RuntimeException( "执行SQL时发生错误" , cause );
        } finally {
            this.release( ps );
        }
    }

    // 将结果集转换为List集合
    private List<Map<String,Object>> transform( ResultSet rs ){
        if( rs == null ) {
            throw new RuntimeException( "结果集为空" );
        }

        List<Map<String,Object>> list = new ArrayList<>();

        ResultSetMetaData meta ;
        try {
            meta = rs.getMetaData();
        } catch (SQLException cause) {
            throw new RuntimeException( "获取结果集元数据时发生错误" , cause );
        }

        try {
            int count = meta.getColumnCount();
            while (rs.next()) {
                Map<String,Object> map = new HashMap<>();
                for( int i = 1; i <= count ; i++ ){
                    String label = meta.getColumnLabel( i ); // 获取列名或别名
                    Object value = rs.getObject( i ); // 获取指定列的值
                    map.put( label, value );
                }
                list.add( map );
            }
            return list ;
        } catch ( SQLException cause ) {
            throw new RuntimeException( "从结果集获取数据时发生错误" , cause );
        }
    }

    // "执行"DQL语句
    public List<Map<String,Object>> query( String sql , Object... params ){
        List<Map<String,Object>> list = null;
        PreparedStatement ps = this.prepare(sql, false);
        setParameters( ps , params );
        ResultSet rs = null;
        try {
            rs = ps.executeQuery();
            list = transform( rs );
        } catch (SQLException cause) {
            throw new RuntimeException("查询失败", cause);
        } finally {
            this.release( rs );
        }
        this.release( ps );
        return list;
    }

    // 执行插入操作并返回由数据库生成的键
    public Object insert( String sql, Object... params ){
        Object id = null ;
        PreparedStatement ps = this.prepare(sql, true);
        setParameters( ps , params );
        try {
            int count = ps.executeUpdate();
            // 如果仅插入一行数据，则只需要返回单个键即可
            if( count == 1 ) {
                ResultSet rs = ps.getGeneratedKeys();
                if( rs.next() ) {
                    id = rs.getObject( 1 );
                }
                this.release( rs );
            }
            return id;
        } catch (SQLException cause) {
            throw new RuntimeException( "执行SQL时发生错误" , cause );
        } finally {
            this.release( ps );
        }
    }

    // 修改事务提交模式
    private void autocommit( boolean auto ){
        try {
            conn.setAutoCommit( auto );
        } catch (SQLException cause) {
            throw new RuntimeException( "修改事务提交模式失败" , cause);
        }
    }

    // 回滚事务
    private void commit(){
        try {
            conn.commit();
        } catch (SQLException cause) {
            throw new RuntimeException( "事务提交失败" , cause);
        }
    }

    // 回滚事务
    private void rollback(){
        try {
            conn.rollback();
        } catch (SQLException cause) {
            throw new RuntimeException( "事务回滚失败" , cause);
        }
    }

    // 在同一个事务中执行多个DML操作
    public void execute( Runner runner ){
         // 关闭事务自动提交
         this.autocommit(false);
         try {
             // 如何执行多个DML语句
             runner.doInTransaction();
             // 手动提交事务
             this.commit();
         } catch (Exception cause) {
             // 手动回滚事务
             this.rollback();
             // 打印异常轨迹
             throw new RuntimeException( "执行失败" , cause);
         }
         // 启用事务自动提交
         this.autocommit(true);
    }

    // 释放资源
    private void release( AutoCloseable ac ) {
        if( ac != null ) {
            try {
                ac.close();
            } catch (Exception cause) {
                String message = String.format( "释放资源时发生错误" );
                throw new RuntimeException( message , cause );
            }
        }
    }

    // 释放资源
    public void release() {
        this.release( conn );
    }
}
